package cn.fyg.service.finance.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import com.sun.rowset.CachedRowSetImpl;

@SuppressWarnings("restriction")
public class DbUtil {
	private static Logger logger = LogManager.getLogger(DbUtil.class.getName()); 
	
	public static Connection getConnection() {
		Connection connection = null;
	    try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@118.178.132.251:1521:fyg";
			String UserName = "eas70";// 数据库用户登陆名
			String Password = "fangyuan_27_USER";// 密码
			connection = DriverManager.getConnection(url, UserName, Password);
		} catch (ClassNotFoundException e) {
			logger.info(e.getMessage());
		} catch (SQLException e) {
			logger.info(e.getMessage());
		}
	    return connection;
	}
	
	public static Connection get33Connection() {
		Connection connection = null;
	    try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//切换到正式库
			String url = "jdbc:oracle:thin:@172.16.1.33:1521:fyg";
			String UserName = "eas70";// 数据库用户登陆名 ( 也有说是 schema 名字的 )
			String Password = "fangyuan_27_USER";// 密码
			connection = DriverManager.getConnection(url, UserName, Password);
		} catch (ClassNotFoundException e) {
			logger.info(e.getMessage());
		} catch (SQLException e) {
			logger.info(e.getMessage());
		}
	    return connection;
	}
	
	/**
	 * 每一次调用都是一个新连接，请考虑使用线程池。
	 * 
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	public static RowSet executeQuery(String sql) throws Exception{
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		CachedRowSet rowset = null;
		try {
			con = getConnection();
			stmt = con.createStatement();
			result = stmt.executeQuery(sql);
			rowset = new CachedRowSetImpl();
			rowset.populate(result);
		} catch (Exception e) {
			throw new Exception(e);
		}finally{
			cleanup(result, stmt, con,null);
		}
		return rowset;
	}
	
	/**
	 * 每一次调用都是一个新连接，请考虑使用线程池。
	 * 
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	public static RowSet execute33Query(String sql) throws Exception{
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		CachedRowSet rowset = null;
		try {
			con = get33Connection();
			stmt = con.createStatement();
			result = stmt.executeQuery(sql);
			rowset = new CachedRowSetImpl();
			rowset.populate(result);
		} catch (Exception e) {
			throw new Exception(e);
		}finally{
			cleanup(result, stmt, con,null);
		}
		return rowset;
	}
	
	
	/**
	 * 每一次调用都是一个新连接，请考虑使用线程池。
	 * 
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	public static RowSet executeQuery(String sql,Object params[]){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet result = null;
		CachedRowSet rowset = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				stmt.setObject(i+1, params[i]);
			}
			result = stmt.executeQuery();
			rowset = new CachedRowSetImpl();
			rowset.populate(result);
		} catch (Exception e) {
			logger.info(e.getMessage());
		}finally{
			cleanup(result, stmt, con,null);
		}
		return rowset;
	}
	
	/**
	 * 每一次调用都是一个新连接，请考虑使用线程池。
	 * 
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	public static RowSet execute33Query(String sql,Object params[]){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet result = null;
		CachedRowSet rowset = null;
		try {
			con = get33Connection();
			stmt = con.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				stmt.setObject(i+1, params[i]);
			}
			result = stmt.executeQuery();
			rowset = new CachedRowSetImpl();
			rowset.populate(result);
		} catch (Exception e) {
			logger.info(e.getMessage());
		}finally{
			cleanup(result, stmt, con,null);
		}
		return rowset;
	}
	
	/**
	 * 执行完查询，Connection不释放，请自行释放连接
	 * 
	 * @param sql
	 * @param params
	 * @param con
	 * @return
	 * @throws Exception
	 */
	public static RowSet executeQuery(String sql,Object params[],Connection con) throws Exception{
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			result = ps.executeQuery();
			CachedRowSet rowset = new CachedRowSetImpl();
			rowset.populate(result);
			return rowset;
		} catch (Exception e) {
			throw new Exception(e);
		}finally{
			cleanup(result, ps, null,null);
		}
		
	}
	
	/**
	 * 关闭数据库连接
	 * @param rs
	 * @param stmt
	 * @param cn
	 */
	public static void cleanup(ResultSet rs, Statement stmt, Connection con,String description) {
		try {
			if(rs  != null){
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();
			}
			String desc = "";
			if (description != null) {
				desc = desc + description;
			}
//			logger.info(desc + " is closed!");
		} catch (Exception e) {
			logger.info("数据库关闭失败，信息："+e.getMessage());
		}
	}
	
	/**
	 * 将list中的元素转化为SQL语句的where条件。
	 * @param list 
	 * @return
	 */
	public static String List2InWhereSQL(List<String> list){
		StringBuffer sb = new StringBuffer();
		sb.append("('");
		for (int i = 0; i < list.size(); i++) {
			sb.append(list.get(i));
			if (i != list.size() -1) {
				sb.append("','");
			}
		}
		sb.append("')");
		return sb.toString();
	}
	
	/** 
	 * 回滚事务 
	 * @param cnn 
	 */  
	public static void rollBackTransaction(Connection cnn){  
	    if(cnn!=null){  
	        try {  
	            if(!cnn.getAutoCommit()){  
	                cnn.rollback();  
	            }  
	        } catch (SQLException e) {  
	        	logger.info("数据库回滚失败，异常信息: "+e.getMessage());
	        }  
	    }  
	}  
}
